/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cafe.Model;

import cafe.DataObject.Bill;
import cafe.DataObject.DataObject;
import cafe.Model.Utility.DBConnection;
import cafe.Model.Utility.DBConnectionPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HoangLong
 */
public class BillModel extends AbstractModel {

    private static BillModel instance = null;

    private DBConnectionPool connectionPool = null;

    /**
     * Get all table and it's informations
     *
     * @return ArrayList<Table> all table in database
     */
    private BillModel() {
        this.connectionPool = DBConnectionPool.getInstance();
    }

    public static synchronized BillModel getInstance() {
        if (instance == null) {
            instance = new BillModel();
        }

        return instance;
    }

    @Override
    public List<? extends DataObject> getAll() {
        List<Bill> result = new LinkedList();

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `bill`;";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query);

            while (rs.next()) {
                Bill bill = new Bill();
                bill.setId(rs.getString("id"));
                bill.setDate(rs.getDate("date"));
                bill.setTotalCost(rs.getLong("totalCost"));
                bill.setIsPaid(rs.getBoolean("isPaid"));
                bill.setCustomerId(rs.getString("customerId"));

                result.add(bill);
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            return result;
        }
    }

    @Override
    public DataObject getByID(String id) {
        Bill bill = null;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `bill` WHERE `id` = ? LIMIT 1;";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query, id);

            while (rs.next()) {
                bill = new Bill();
                bill.setId(rs.getString("id"));
                bill.setDate(rs.getDate("date"));
                bill.setTotalCost(rs.getLong("totalCost"));
                bill.setIsPaid(rs.getBoolean("isPaid"));
                bill.setCustomerId(rs.getString("customerId"));

            }

        } catch (SQLException ex) {
            Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
            }

            this.connectionPool.giveBack(connectionObject);

            return bill;
        }
    }

    @Override
    public int insert(DataObject record) {
        Bill bill = (Bill) record;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {
            Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        //NOTE: we must use ` ` to cover table name 
        String query = "INSERT INTO `bill`(`id`,`date`, `customerId`, `totalCost`, `isPaid`) "
                + "VALUES (?,?,?,?,?); ";

        int result = 0;

        try {
            result = connectionObject.excute(query, bill.toObjectArray());

        } catch (SQLException ex) {
            Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

    /**
     * insert a DataObject without id is given Note:its id must auto-create in
     * database ms
     *
     * @param record a DataObject whose id is unnecessary
     * @return id (primary key) of this new insert record
     */
    public int insertWithAutoGeneratedKey(Bill record) {

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();
        } catch (Exception ex) {
            Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        //NOTE: we must use ` ` to cover table name 
        String query = "INSERT INTO `bill`(`date`, `customerId`, `totalCost`, `isPaid`) "
                + "VALUES (?,?,?,?); ";
        //+ "SELECT LAST_INSERT_ID();";

        int result = -1;
        ResultSet resultSet = null;
        try {
            resultSet = connectionObject.excuteGeneratedKey(query,
                    record.getDate(), record.getCustomerId(), record.getTotalCost(), record.isPaid());
            if (null != resultSet && resultSet.next()) {

                result = resultSet.getInt(1);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
            }

            //give back connection
            this.connectionPool.giveBack(connectionObject);

            return result;
        }

    }

    @Override
    public int edit(String fromRecordId, DataObject toRecord) {
        Bill bill = (Bill) toRecord;

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "UPDATE `bill` "
                + "SET `date`=?,`totalCost`=?,`isPaid`=?, `customerId=`? "
                + "WHERE `id` = ?";

        int result = 0;

        try {
            connectionObject.excute(query, bill.getDate(), bill.getTotalCost(), bill.isPaid(), bill.getCustomerId(),
                    fromRecordId);

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

    @Override
    public int delete(String id) {
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "DELETE FROM `bill` WHERE `id` = ?;";

        int result = 0;

        try {
            connectionObject.excute(query, id);

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            return result;
        }
    }

}
